﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Data.OracleClient;


namespace CCPSIP
{
    public class OracleErpKit
    {
        /// <summary>取得Oracle連線字串(SIP_CONTACT)</summary>
        private static string GetConnStrErpContact()
        {
            return "SERVER=(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = 203.69.132.121)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 203.69.132.122)(PORT = 1521))(LOAD_BALANCE = yes)(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = erp)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 10))));uid=AC;pwd=T5001855";
        }

        /// <summary>新增圖片紀錄</summary>
        public static void InsertPhotoRecordWrapper(string SystemKind, string KeyNumber, string[] Files, string Creater, string Remark)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrErpContact());
            ConnOracle.Open();
            OracleTransaction Trans = ConnOracle.BeginTransaction();

            try
            {
                foreach (string file in Files)
                {
                    FileStream fs = new FileStream(file, FileMode.Open);
                    byte[] ByteImage = new byte[fs.Length];
                    fs.Read(ByteImage, 0, Convert.ToInt32(fs.Length));
                    fs.Dispose();

                    InsertPhotoRecord(ConnOracle, Trans, SystemKind, KeyNumber, ByteImage, Creater, Remark, Path.GetFileName(file));
                }

                Trans.Commit();
            }
            catch (Exception ex)
            {
                if (Trans != null)
                {
                    Trans.Rollback();
                }
                throw ex;   //ex往上層丟, 下面的finally還是會執行
            }
            finally
            {
                ConnOracle.Dispose();
            };
        }
        private static void InsertPhotoRecord(OracleConnection ConnOracle, OracleTransaction Trans, string SystemKind, string KeyNumber, byte[] Image, string Creater, string Remark, string FileName)
        {
            string cmd_all = null;
            if (String.IsNullOrEmpty(Remark))
            {
                cmd_all = "INSERT INTO ERP_PAPER_IMAGE(SYSTEM_KIND, KEY_NUMBER, IMAGE, CREATER, CREATE_TIME, FILE_NAME)"
                               + " VALUES(:SYSTEM_KIND, :KEY_NUMBER, :IMAGE, :CREATER, sysdate, :FILE_NAME)";
            }
            else
            {
                cmd_all = "INSERT INTO ERP_PAPER_IMAGE(SYSTEM_KIND, KEY_NUMBER, IMAGE, CREATER, CREATE_TIME, REMARK, FILE_NAME)"
                               + " VALUES(:SYSTEM_KIND, :KEY_NUMBER, :IMAGE, :CREATER, sysdate, :REMARK, :FILE_NAME)";
            }
            OracleCommand Cmd = new OracleCommand(cmd_all, ConnOracle, Trans);

            Cmd.Parameters.Add(":SYSTEM_KIND", OracleType.VarChar).Value = SystemKind;
            Cmd.Parameters.Add(":KEY_NUMBER", OracleType.VarChar).Value = KeyNumber;
            Cmd.Parameters.Add(":IMAGE", OracleType.Blob).Value = Image;
            Cmd.Parameters.Add(":CREATER", OracleType.VarChar).Value = Creater;
            Cmd.Parameters.Add(":FILE_NAME", OracleType.VarChar).Value = FileName;
            if (!String.IsNullOrEmpty(Remark))  //Remark can be null
                Cmd.Parameters.Add(":REMARK", OracleType.VarChar).Value = Remark;

            Cmd.ExecuteNonQuery();
        }

        public static bool HasPhotoRecord(string SystemKind, string KeyNumber)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrErpContact());
            string cmd_all = "SELECT FILE_NAME FROM ERP_PAPER_IMAGE"
                           + " WHERE SYSTEM_KIND = :SYSTEM_KIND"
                           + " AND KEY_NUMBER = :KEY_NUMBER";

            OracleCommand Cmd = new OracleCommand(cmd_all, ConnOracle);
            Cmd.Parameters.Add(":SYSTEM_KIND", OracleType.VarChar).Value = SystemKind;
            Cmd.Parameters.Add(":KEY_NUMBER", OracleType.VarChar).Value = KeyNumber;

            ConnOracle.Open();
            string s = Convert.ToString(Cmd.ExecuteScalar());
            ConnOracle.Dispose();

            if (string.IsNullOrEmpty(s))
                return false;
            else
                return true;
        }

        public static void DeletePhotoRecord(string SystemKind, string KeyNumber)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrErpContact());
            string cmd_all = "DELETE FROM ERP_PAPER_IMAGE"
                           + " WHERE SYSTEM_KIND = :SYSTEM_KIND"
                           + " AND KEY_NUMBER = :KEY_NUMBER";

            OracleCommand Cmd = new OracleCommand(cmd_all, ConnOracle);
            Cmd.Parameters.Add(":SYSTEM_KIND", OracleType.VarChar).Value = SystemKind;
            Cmd.Parameters.Add(":KEY_NUMBER", OracleType.VarChar).Value = KeyNumber;

            ConnOracle.Open();
            Cmd.ExecuteNonQuery();
            ConnOracle.Dispose();
        }
    }
}